IF函数详解,如何用Excel、Python、SQL、Tableau实现IF函数功能 |
您所在的位置:网站首页 › excel 返回值 › IF函数详解,如何用Excel、Python、SQL、Tableau实现IF函数功能 |
0
分享至
用微信扫码二维码 分享至好友和朋友圈 IF函数详解,Excel+Python+SQL+Tableau四个工具同时登场 IF 函数是 Excel 中最常用的函数之一,它可以对值进行逻辑比较。这个函数的语法非常符合人类语言, “如果……就……否则……” 比如说如果你喜欢我,我们就结婚,否则就不结婚 用IF来实现就是=IF(“you love me”,”we are married”,”we aren’t married”) 使用逻辑函数 IF 函数时,如果条件为真,该函数将返回一个值;如果条件为假,函数将返回另一个值。 第一个参数是条件真假值,第二个参数是条件为真时的返回值,第三个参数是条件为假时的返回值 这个函数还可以写成无限嵌套模式(俗称套娃),IF(IF(IF(IF,……,但是个人不建议这样写,因为这种写法实在是太容易给自己弄迷糊,如果是多条件判断,可以考虑IFS,如果只是取值判断建议写成IF()+IF()+IF() 单IF函数相加这种形式完成 本例主要解决以下问题: 1如果成绩大于等于600分,则返回"优秀",否则返回"普通" 2如果成绩大于等于600分,则返回"优秀",大于等于500分小于600分返回"普通",小于500分返回"不优秀" 3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀" 4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通" 5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ” 6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名 实现方式:Excel、Python、SQL、Tableau 一、Excel实现 1如果成绩大于等于600分,则返回优秀,否则返回普通 在J2单元格输入公式:=IF(I2>=600,优秀,普通) 2如果成绩大于等于600分,则返回优秀,大于等于500分小于600分返回普通,小于500分返回"不优秀" 在J2单元格输入公式:=IF(I2>=600,优秀,IF(I2>=500,普通,"不优秀")) 说明:此时一共有两个IF函数,里面嵌套了一个,这个语句先判断第一个IF,如果大于等于600,则返回优秀,然后把剩下的小于600的单元格区域丢给第二个IF函数判断,因为此时这里面都是小于600分的单元格了,所以只需要在判断条件处写大于等于500即可,没必要再加小于600分的条件,然后进行判断,把里面500分及以上的返回普通,剩下的丢给后面,如果还有IF的话就再继续判断,没有的话,则统一返回最后一个IF函数里面的第三个参数,这里也就是"不优秀",如果不用嵌套,改用IFS函数如何实现本例呢? 改用IFS函数实现,在J2单元格输入公式:=IFS(I2>=600,优秀,I2>=500,普通,I2 用法:IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3) 这里面最少要有两个参数,第一个参数是判断条件,第二个参数是返回值,最多可以判断127个条件,也就是254个参数,和SUM求和的参数极限类似 IFS 函数检查是否满足一个或多个条件,且返回符合第一个TRUE 条件的值,判断时是按顺序依次进行,这个函数可以取代多个嵌套 IF 语句,并且有多个条件时更方便阅读 如果找不到 TRUE 条件,则此函数返回 #N/A! 错误,例如只输入一个大于600分的条件判断:=IFS(I2>=600,"优秀") 这时小于600分的部分会返回#N/A,也就是查不到 此时我们需要前面加一个IFERROR进行修复一下,比如小于600分的返回普通,输入公式:=IFERROR(IFS(I2>=600,"优秀"),"普通") IFERROR用法说明:IFERROR(value, value_if_error) 第一个参数是正常返回的值,当第一个参数无法返回值时返回第二个参数作为结果 还有一个函数IFNA(),用法基本和这个一样 这里相当于用了一个复合函数,首先IFS(I3>=600,"优秀") 作为IFERROR函数的第一个参数,这里可以完成大于等于600分以上条件的返回,这个参数IFERROR并不理会,而只有当这个条件不能返回时,即前面所说的查不到的#N/A部分,IFERROR函数会自动把这一部分的单元格返回第二个参数,这里面也就是返回“普通” 3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀" 输入公式=IFERROR(IFS(AND(B11="男",I11>600),"男优秀",AND(B11="女",I11>600),"女优秀"),"普通") 说明:AND和OR函数 AND是且判断,都为真时为真,只要有一个为假则为假,AND(1=1,2=2,3=3,4=4)为真,AND(1=1,2=2,3=3,4=5)为假 OR是或判断,都为假时为假,只要有一个为真则为真,OR(1=2,3=4,5=6,7=8)为假,OR(1=2,3=4,5=6,7=7)为真 这两个一般都需要配合前面的IF函数进行使用,应该很少有单独使用场景 4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通" 输入公式:=IF(OR(E2>120,F2>120),"单科优秀","普通") 5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ” 输入公式:=IFERROR(IFS(ISNUMBER(FIND("剑",E2)),"剑法",ISNUMBER(FIND("刀",E2)),"刀法"),"其他") IS类函数返回逻辑值:ISNUMBER 搭配 FIND函数使用,可以对指定单元格以判断是否存在关键字词进行对应返回 类似的函数还有以下: FIND用法说明:FIND(find_text, within_text, [start_num]) 第一个参数是要查找的关键字,这里我们输入的是“剑”或者“刀”,此参数严格区分大小写,并且不允许使用通配符 第二个参数是要在哪个文本里查找,我们这里在技能列查找,比如田伯光的技能“狂风刀法” 第三个参数可选,是从第几个开始查询,默认为1 拆解公式:FIND("刀","狂风刀法",1) 这个公式返回的是一个数字3,也就是说刀字在这个单元格字符里面的第3位,只要能返回数字不管在第几位,也就意味着这个单元格里包含有我们要查找的关键字 然后我们再用ISNUMBER函数把这个数字3转化为逻辑值真,为IFS函数提供参数用 ISNUMBER(FIND("刀","狂风刀法",1))返回值 为TRUE 我们可以看到除了田伯光技能’狂风刀法‘里面含有“刀”字,返回的是刀法,其他大多为剑法 这个函数用处场景较多,之前我曾见过某同事在处理大量数据时,用筛选包含关键字的形式一次一次地手动输入返回值,如果能用这个函数则会极大提高数据处理效率 6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名 FIND查找函数配合字符提取函数LEFT,RIGHT,MID等,可以实现强大的字符提取功能: 从以下字符串中提取省名,=MID(A1,1,FIND(“省”,A1)) 我们发现如果用LEFT进行向左直接取数字几位的话无法实现,因为有的省是两个字,有的是三个字,这时我们就需要借助FIND对每一行的省名有几个字符进行判断 FIND("省",A1))返回值显示包括省字在内的省名个数,比如前面两条返回的是3,黑龙江的返回的为4 MID用法:MID(text, start_num, num_chars) 第一个参数表示,要从哪个字符串里提取,这里我们取A列 第二个参数表示,从第几个字符开始提取,这里我们填1,表示从头进行提取 第三个参数表示,提取多少个字符,这里正是FIND函数起作用的地方 如前,假如提取省名不包括省字本身,则只需要在第三个参数后面减1即可,也就是提取字符少一位 =MID(A1,1,FIND("省",A1)-1) 这里我们也可以使用LEFT函数,=LEFT(A1,FIND("省",A1)) LEFT函数只有两个参数,第一个是要提取的单元格,第二个是提取的位数,从左 同理:RIGHT函数两个参数,第一个是要提取的单元格,第二个是提取的位数,从右 如果把大庆市改为齐齐哈尔市,这回我们提取后面的市名,公式:=MID(A1,FIND("省",A1)+1,10) 这里FIND函数传递的值是作为MID函数的第二个参数,也就是从第几位开始提取,FIND("省",A1)+1表示出现省字后面第一个字符的位数,第三个参数可以写一个比较大的数字,只要提取的位数小于这个数值,则默认全部提取到最后 还可以用RIGHT提取:=RIGHT(A1,LEN(A1)-FIND("省",A1)) 这里RIGHT的第二个参数是用了计算得来了,省名的位数FIND("省",A1),LEN(A1)表示全部的位数,两者相减得到的则是要提取的市名的位数 还可以再升级,后面加上县名等,我们从中间提取市名=MID(A1,FIND("省",A1)+1,FIND("市",A1)-FIND("省",A1)) 说明: FIND("省",A1)+1 表示市名第一个文本出现在第几位上 FIND("市",A1)-FIND("省",A1) 表示市名文本的字符长度,例如第一个,FIND("市",A1)=6表示市字出现在第6位,FIND("省",A1)=3,省字出现在第三位,两者的差=6-3=3 则是“铁岭市”的字符长度 二、Python实现: 1如果成绩大于等于600分,则返回"优秀",否则返回"普通" 代码如下: def score_if(score): if score >=600: a = "优秀" else: a = "普通" return a df["是否优秀"] = df["总成绩"].apply(lambda x:score_if(x)) 这种我们是用lambda表达式实现: 前面自定义一个函数,score_if 进行逻辑判断取数 df["是否优秀"] = df["总成绩"].apply(lambda x:score_if(x)) 这条语句相当于把总成绩那列应用到函数score_if,然后把返回值赋给”是否优秀”列 附:本例还可以用一行代码完成,使用lambda匿名函数,这是一种没有名称的函数 df["是否优秀"] = df["总成绩"].apply(lambda x: "优秀" if x>=600 else "普通") 2如果成绩大于等于600分,则返回"优秀",大于等于500分小于600分返回"普通",小于500分返回"不优秀" 代码如下: def score_if(score): if score >=600: a = "优秀" elif score>=500: a = "普通" else: a = "不优秀" return a df["是否优秀"] = df["总成绩"].apply(lambda x:score_if(x)) 这里第9行代码也可以写成这样 df["是否优秀"] = df["总成绩"].apply(score_if) appy 是应用的意思,这行表示把总成绩列作为score_if函数的参数,然后函数返回值赋给是否优秀列 如果用lambda函数,一行代码: df["是否优秀"] = df["总成绩"].apply(lambda x: "优秀"if x>=600 else("普通"if x>=500 else"不优秀")) 3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀" 代码如下: def score_if(score,sex): if score >=600 and sex=="男": a = "男优秀" elif score >=600 and sex=="女": a = "女优秀" else: a ="普通" return a df = pd.read_excel("c:/study_note/xiao_if.xlsx",sheet_name="成绩单") df['是否优秀'] = df.apply(lambda df:score_if(df['总成绩'],df['性别']),axis=1) 附:还有一种写法如下 def score_if(score,sex): if score >=600 and sex=="男": a = "男优秀" elif score >=600 and sex=="女": a = "女优秀" else: a ="普通" return a df = pd.read_excel("c:/study_note/xiao_if.xlsx",sheet_name="成绩单") df["是否优秀"] = None for i in range(len(df)): df["是否优秀"][i] = score_if(df["总成绩"][i],df["性别"][i]) 这里用到了条件且在自定义函数里面,因为自定义函数有两个参数,我再后面用了一个for循环语句 第10行:新建一个字段,命名为‘是否优秀’ 第11行,让变量i进行循环,len(df)表示原表的行数,等于18,range(18)相当于让i从0取到17 第12行,把每一行的总成绩列值作为函数的第一个参数,性别列值作为第二个参数,然后把返回值赋给是否优秀列,因为i是变量,从0到17(意义表示从第1行到第18行) 在python中and和or函数,布尔类型也是只有True和False两种取值,python中的布尔运算符and,or,not全是小写 逻辑运算和Excel一样 但是当用pandas库的数据类型 DataFrame 和Series 时进行逻辑运算时,此时运算符变成 & 表示 and 且 | 表示 or 或 ~ 表示not 非 4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通" 代码: def score_if(chi,math): if chi >=120 or math >=120: a = "单科优秀" else: a ="普通" return a df = pd.read_excel("c:/study_note/xiao_if.xlsx",sheet_name="成绩单") df['是否优秀'] = df.apply(lambda df:score_if(df['语文'],df['数学']),axis=1) 5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ” 代码如下: df = pd.read_excel("c:/study_note/xiao_if.xlsx",sheet_name="花名册") col_name = df.columns.tolist() col_name.insert(col_name.index("技能")+1,"技能类型") df = df.reindex(columns=col_name) def tech(x): if x.find("剑") >=0: a = "剑法" elif x.find("刀")>=0: a = "刀法" else: a = "其他" return a df['技能类型'] = df['技能'].apply(tech) 第3行:把原表所有列名转换为列表格式,并传递给变量col_name 第4行:在列名列表里‘技能’文本后面新插入一个文本‘技能类型’,作为一个新的字段列名 第5行:把新增加的字段传递给原表df 第6行: x.find(参数),表示在x单元格查找参数所有的位置,如果单元格里存在参数,则返回位置数字,如果不存在则返回-1,如果参数在单元格第1位,则返回0,因为在python里顺序是从0开始,0代表第一位,x.find("剑") >=0表示单元格值里面存在“剑”这个关键词,相当于Excel时面函数ISNUMBER(FIND("剑","独孤九剑",1))返回值为TRUE一样 6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名 代码如下: df["省"] = df["地址"].apply(lambda x: x[0:x.find("省")+1]) df["市"] = df["地址"].apply(lambda x: x[x.find("省")+1:x.find("市")+1]) df["县"] = df["地址"].apply(lambda x: x[x.find("市")+1:]) 这里直接用了python里面的切片(slicing)语法:sequence[start:stop:step] sequence是序列的意思,第一个参数是起始位置,第二个参数是结束位置(不包括本身,相当于数学里面的左闭右开区间),第三个参数是步长,举例:a = “辽宁省铁岭市昌图县八面城镇“在本例中文本值作为一个序列 a[0:3]表示:从第1位开始取3位(0,1,2) a[:3]表示:和上面一样,只是省略了第1个参数 a[::]表示全取,三个参数都省略了 a[-4:] 表示从右边第三位开始向右取数取到最后 a[::2]表示每两个元素取一个 a[-1:-5:-1]表示从右往左取4位,这里面第二个参数和第三个参数都是负数,第二个参数相当于从右往左数第5位(是开区间不取本身),第三个参数1是步长,每个都取,负号表示从右往左取 三、SQL实现方法: 1如果成绩大于等于600分,则返回"优秀",否则返回"普通" 代码如下: SELECT 姓名,总成绩, IF(总成绩>=600,"优秀","普通") AS 是否优秀FROM score ; 2如果成绩大于等于600分,则返回"优秀",大于等于500分小于600分返回"普通",小于500分返回"不优秀" 代码如下: SELECT 姓名,总成绩, IF(总成绩>=600,"优秀",IF(总成绩>=500,"普通","不优秀")) AS 是否优秀FROM score ; 上面这条查询也可以写成下面这样: SELECT 姓名,总成绩, CASEWHEN 总成绩>=600THEN"优秀" WHEN 总成绩>=500THEN"普通" ELSE"不优秀" ENDAS 是否优秀 FROM score ; 3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀" 代码如下: SELECT 姓名,总成绩, CASEWHEN 总成绩>=600AND 性别="男"THEN"男优秀" WHEN 总成绩>=600AND 性别='女'THEN"女优秀" ELSE"普通" ENDAS 是否优秀 FROM score ; 在MYSQL中AND 和 OR函数,布尔类型也是只有True和False两种取值 NOT 符号是! 非 AND 符号是 && 且 OR 符号是|| 或 XOR 表示异或 a XOR b 这个逻辑运算相当于 a AND (NOT b) 或者 (NOT a) AND b 算法与Excel一样 4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通": SELECT 姓名,语文,数学, CASEWHEN 语文>=120OR 数学>=120THEN"单科优秀" ELSE"普通" ENDAS 是否优秀 FROM score ; 5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ” 代码如下: SELECT 姓名,技能, CASEWHEN 技能 REGEXP"[刀]"THEN"刀法" WHEN 技能 REGEXP"[剑]"THEN"剑法" ELSE"其他" ENDAS 技能类型 FROM names; 6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名 代码如下: SELECT 地址, MID(地址,1,LOCATE("省",地址)) AS 省, MID(地址,LOCATE("省",地址)+1,locate("市",地址)-LOCATE("省",地址)) AS 市, MID(地址,LOCATE("市",地址)+1,locate("县",地址)-LOCATE("市",地址)) AS 县 FROM temp3; 这里MID函数不同于Excel,SQL里是可以引入负数作为第二个参数,代表的意思是从右往左,locate本身就是定位的意思,这个函数相当于Excel里的FIND函数 比如SELECTmid("basketball",-4);这里省略了第三个参数,从这个字符最右往左数第4位开始取数,一直向右取到完 SELECTmid("basketball",-4,2);如果加上第三个参数,从这个字符最右往左数第4位开始取数,取数长度为2位 本例还可以用LEFT 和RIGHT函数进行提取:代码如下 SELECT 地址, LEFT(地址,LOCATE("省",地址)) AS 省, MID(地址,LOCATE("省",地址)+1,locate("市",地址)-LOCATE("省",地址)) AS 市, RIGHT(地址,CHAR_LENGTH(地址)-LOCATE("市",地址)) AS 县 FROM temp3; 四、Tableau实现: 1如果成绩大于等于600分,则返回"优秀",否则返回"普通" 打开Tableau软件,连接到工作表成绩单,然后创建一个计算字段输入公式: IF [总成绩] >= 600 THEN "优秀" ELSE "不优秀" END 2如果成绩大于等于600分,则返回"优秀",大于等于500分小于600分返回"普通",小于500分返回"不优秀" 计算字段代码如下: IF [总成绩] >=600 THEN "优秀" ELSEIF [总成绩] >=500 THEN "普通" ELSE "不优秀" END 3如果成绩大于600分并且性别为男,则返回"男优秀",如果成绩大于600分且性别为女,返回"女优秀",否则返回"不优秀" 计算字段代码如下: IF [总成绩] >=600 AND [性别]=='男' THEN "男优秀" ELSEIF [总成绩] >=600 AND [性别]=='女' THEN "女优秀" ELSE "普通" END 4如果语文成绩大于120分,或者数学成绩大于120分,则返回"单科优秀",否则返回"普通" 代码如下: IF [语文] >=120 OR [数学] >=120 THEN "单科优秀" ELSE "普通" END 5花名册里技能字段如果包含“剑”字则返回“剑法”,如果包含“刀”字,则返回“刀法”,除此之外返回“其他 ” 新建Tableau工作簿,并连接到花名册表,新建计算字段技能类型,代码如下: IF CONTAINS([技能],"剑") THEN "剑法" ELSEIF CONTAINS([技能],"刀") THEN "刀法" ELSE "其他" END CONTAINS函数是Tableau特有的,返回的也是一个布尔逻辑值,相当于SQL中的WHEN 技能 REGEXP"[刀]" (这里是正则查找,返回的也是逻辑值),也相当于Excel中的ISNUMBER(FIND("刀","狂风刀法",1)) 6从字符串里提取指定位置的文本,本例从地址列文本里面进行提取省名、市名、县名 代码: 计算字段省:MID([地址],1,FIND([地址],"省")) 计算字段市:MID([地址],FIND([地址],"省")+1,FIND([地址],"市")-FIND([地址],"省")) 计算字段县:MID([地址],FIND([地址],"市")+1,LEN([地址])-FIND([地址],"市")) 需要注意,Tableau中的FIND函数和Excel中的FIND函数两个参数的位置正好相反 到此四种工具全部完成任务,如果觉得有用请点下关注,可以和本人互动交流,共同学习进步 特别声明:以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布,本平台仅提供信息存储服务。 Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services. /阅读下一篇/ 返回网易首页 下载网易新闻客户端 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |